<%@page import="com.alex.common.security.SimpleAuthor"%>
<%@ page contentType="text/html;charset=UTF-8" language="java"%>

<%@page import="com.alex.mda.jdbc.TableViewService"%>
<%@page import="com.alex.mda.jdbc.meta.Column"%>
<%@page import="com.alex.mda.jdbc.meta.Table"%>
<%@page import="com.alex.mda.jdbc.meta.Catalog"%>
<%@page import="java.util.Map"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.util.Set"%>
<%@page import="com.alex.mda.jdbc.MetaService"%>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>

<%
    		String conn = request.getParameter("conn");
			String catalog = request.getParameter("catalog");
			String table = request.getParameter("table");
			String op = request.getParameter("op");
			String sql = request.getParameter("sql");
			
			MetaService service=MetaService.getInstance();
			TableViewService tableService=TableViewService.getInstance();
			
			StringBuilder optionStr=new StringBuilder();
			
			if (conn == null || "".equals(conn)) {
			    //default index
		        Set<String> connNameSet = service.getConnNames();
		        for (String connName : connNameSet) {
		            optionStr.append(String.format("<option value='%s'>%s</option>",connName,connName));
		        }
		        request.setAttribute("optionStr",optionStr.toString());
			}
			else if (conn != null && catalog == null) {
			    //show databases
				Connection connection = service.getConn(conn);
			    Map<String, Catalog> catalogMap = service.getCatalogs(connection);
			    for (String catalogName : catalogMap.keySet()) {
			        optionStr.append(String.format("<option value='%s'>%s</option>",catalogName,catalogName));
			    }
			    response.getWriter().write(optionStr.toString());
			    response.flushBuffer();
			    return;
			}
			else if (conn != null && catalog != null && table == null && op == null) {
			    //show tables
				Connection connection = service.getConn(conn);
				Map<String, Table> tableMap = service.getTables(connection,catalog);
			    for (String tableName : tableMap.keySet()) {
			        String tableRemarks=((Table)tableMap.get(tableName)).getRemarks();
			        if(tableRemarks==null || "".equals(tableRemarks)){
			            tableRemarks=tableName;
			        }
			        optionStr.append(String.format("<option value='%s'>%s</option>",tableName,tableRemarks));
			    }
			    response.getWriter().write(optionStr.toString());
			    response.flushBuffer();
			    return;
			}
			else if (conn != null && catalog != null && table != null && "showtable".equals(op)) {
			    String tableResult=tableService.showTable(conn,catalog,table);
			    response.getWriter().write(tableResult);
			    response.flushBuffer();
			    return;
			}
			else if (conn != null && catalog != null && table != null && "createquery".equals(op)) {
			    String sqlStr=tableService.createQuerySQL4MySQL(conn,catalog,table);
			    response.getWriter().write(sqlStr);
			    response.flushBuffer();
			    return;
			}
			else if (conn != null && catalog != null && table != null && sql != null && "executequery".equals(op)) {
			    String queryresult=tableService.exeSQL(conn,catalog,sql);
			    response.getWriter().write(queryresult);
			    response.flushBuffer();
			    return;
			}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>数据库表查询 - MDA</title>

<!-- conf: controller -->
<c:if test="${mdadmin!='mda'}">
	<c:redirect url="http://www.sina.com.cn" />
</c:if>
<!-- conf: jquery.js -->
<script type="text/javascript" src="/static/js/jquery-1.6.2.js"></script>
<!-- conf: var in js -->
<script type="text/javascript">
	//<//%=request.getContextPath()%>
	var appPath = "/mda";
</script>

</head>
<body>

	<table cellpadding="2" cellspacing="2">
		<thead>
			<tr style="font-weight: bold; color: white; background: black;">
				<td>Connection</td>
				<td>Catalog</td>
				<td>Schema</td>
				<td>Table</td>
				<td>OP</td>
			</tr>
		</thead>
		<tbody>
			<tr>
				<td><select id="conn">
						<option value="- Select Connection -">- Select Connection
							-</option> ${optionStr}
				</select></td>
				<td><select id="catalog">
						<option></option>
				</select></td>
				<td><select id="schema">
						<option></option>
				</select></td>
				<td><select id="table">
						<option></option>
				</select></td>
				<td><input type="button" id="showtable" value="ShowTable" /> |
					<input type="button" id="createquery" value="CreateQuery" /> | <input
					type="button" id="executequery" value="ExecuteQuery" /></td>
			</tr>
			<tr>
				<td colspan="5">SQL: <textarea cols="60" rows="5" id="sql"></textarea></td>
			</tr>
		</tbody>
	</table>

	<div id="queryresult"></div>

	<script type="text/javascript">
		var emptyOption = "<option></option>";
		var connVal = "";
		var catalogVal = "";
		var tableVal = "";

		function getVal() {
			connVal = "";
			catalogVal = "";
			tableVal = "";
			connVal = $("#conn").val();
			if ("- Select Connection -" == connVal) {
				connVal = "";
				return;
			}
			catalogVal = $("#catalog").val();
			tableVal = $("#table").val();
		}

		$(function() {
			var conn = $('#conn');
			if (conn) {
				$('#conn').live(
						'change',
						function() {
							getVal();
							$.post(appPath, "&conn=" + connVal, function(data,
									textStatus) {
								$("#catalog").html(data);
							});
							$("#schema").html(emptyOption);
							$("#table").html(emptyOption);
						});
			}
			;
		});

		$(function() {
			var catalog = $('#catalog');
			if (catalog) {
				$('#catalog').live(
						'change',
						function() {
							getVal();
							$.post(appPath, "&conn=" + connVal + "&catalog="
									+ catalogVal, function(data, textStatus) {
								$("#schema").html("<option>Default</option>");
								$("#table").html(data);
							});
						});
			}
			;
		});

		$(function() {
			var showtable = $('#showtable');
			if (showtable) {
				$('#showtable')
						.live(
								'click',
								function() {
									getVal();
									if (tableVal == "" || catalogVal == ""
											|| connVal == "") {
										alert("Please select Connection/Catalog/Table.");
										return;
									}
									$.post(appPath, "&conn=" + connVal
											+ "&catalog=" + catalogVal
											+ "&table=" + tableVal
											+ "&op=showtable", function(data,
											textStatus) {
										$("#queryresult").html(data);
									});
								});
			}
			;
		});

		$(function() {
			var createquery = $('#createquery');
			if (createquery) {
				$('#createquery')
						.live(
								'click',
								function() {
									getVal();
									if (tableVal == "" || catalogVal == ""
											|| connVal == "") {
										alert("Please select Connection/Catalog/Table.");
										return;
									}
									$.post(appPath, "&conn=" + connVal
											+ "&catalog=" + catalogVal
											+ "&table=" + tableVal
											+ "&op=createquery", function(data,
											textStatus) {
										$("#sql").val($.trim(data));
									});
								});
			}
			;
		});

		$(function() {
			var executequery = $('#executequery');
			if (executequery) {
				$('#executequery')
						.live(
								'click',
								function() {
									getVal();
									var sql = $("#sql").val();
									if (tableVal == "" || catalogVal == ""
											|| connVal == "" || sql == "") {
										alert("Please input Connection/Catalog/Table/SQL.");
										return;
									}
									$.post(appPath, "&conn=" + connVal
											+ "&catalog=" + catalogVal
											+ "&table=" + tableVal + "&sql="
											+ sql + "&op=executequery",
											function(data, textStatus) {
												$("#queryresult").html(data);
											});
								});
			}
			;
		});
	</script>

</body>
</html>